[TOC]
T-SQL : API in UWP
Background Library
1.At least we need two namespace in UWP if a Sql API is used:
using System.Data.SqlClient;
using System.Diagnostics;
Besides, to archive data binding, sometimes System.Collections.ObjectModel;
is needed, too.
SqlClient
provides almost every API we will need then , and Diagnostics
provides a debug class we will use to show error info. It will transport errors in Sql Server back to us.
At the last but not the least , we need the user id and password and the name of sql server we are going to access:
private string connectionString =
@"Data Source=MYCOMPUTER;Initial Catalog=Note; User Id=note; Password=note";
//@"SERVER = ;DATABASE = Note; USER ID = note; PASSWORD = note";
public string ConnectionString { get => connectionString; set => connectionString = value; }
Here we use note
,note
,as our user name and password to access table Note
in MYCOMPUTER
.
Run a Sql Query
2.To run any query commend , just follow this schema:
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = Query;
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception eSql)
{
Debug.WriteLine("Exception:" + eSql.Message);
}
Two class are used : SqlConnection
andSqlCommand
, the former create a connection , and the latter set a buff ready to run its query. (Saved in SqlCommand.CommandText
)
As for string Query
, just assembly it as the query in T-SQL, for example:
string Query = "insert into users values(" + "'"+RegNameText.Text+"'" + "," + "'"+ RegPassword.Password+"'" + ")";
Run a Sql Query and get its returned SELECT
3.Assuming we have a custom class User
,which has 2 member: password and username ,and we want our query fetch every password and username :
class User
{
public string UserID { get; set; }
public string Password { get; set; }
}
This schema is provided from microsoft :
this.InitializeComponent();
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
if(conn.State==System.Data.ConnectionState.Open)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GetUsersQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
var user = new User();
user.UserID = reader.GetString(0).TrimEnd();
user.Password = reader.GetString(1).TrimEnd();
Users.Add(user);
//Users can be a List (List<User>) ,or an observeablecollection typ.
}
}
}
}
}
}
catch(Exception eSql)
{
Debug.WriteLine("Exception:" + eSql.Message);
}
UWP: Data Binding
Now we have a list of custom class filled with our query result , how do we make our Component to show each member’s attributes or values?
We need to use an observeablecollection
and a ListView
component or any other components that support binding. Assuming the list’s name is TestCollection
A ListView should have such structure:
<ListViewItemsSource="{x:Bind TestCollection}" Name="MyListView" SelectionChanged="MyListView_SelectionChanged"> <!--Put list name here-->
<ListView.ItemTemplate>
<DataTemplate x:DataType="assets:Note" ><!--Put your class here-->
<StackPanel><!--Put every attributes of class in sub-components-->
<TextBlock xml:space="preserve" Height="auto"
Text="{x:Bind Content}"
ScrollViewer.VerticalScrollBarVisibility="Auto"/>
<TextBlock Text="{x:Bind Date}"/>
<TextBlock Text="{x:Bind Writter}"/>
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
Then, back to .xmal.cs file, we will create the list we mentioned at first:
private ObservableCollection<Note> TestCollection;//this should be in the Page class
And fill it’s value in current page’s init function, and everything is done.